﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using System.Data.SqlClient;
using CNCSoft.EData;
using CNCSoft.EWinform;

using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using Common;


namespace CNCSoft.ErpApps.TMS.Counsellor
{
    public partial class frmLyLichHocSinh : Form
    {
        public frmLyLichHocSinh()
        {
            InitializeComponent();
        }

        private SqlDataAdapter dataAdapter = new SqlDataAdapter();
        DataSet ds = new DataSet();
        

        private void frmLyLichHocSinh_Load(object sender, EventArgs e)
        {
            GetData("", true);
            txtMaNienHoc.Text = GlobalVariable.WSInfo.getAppProperty("MaNienHocDefault");


            //Datasource cho cac lookup control
            if (ds.Tables["DM_PHUONGXA"] != null)
                ds.Tables["DM_PHUONGXA"].Clear();
            dataAdapter = new SqlDataAdapter("SELECT * FROM DM_PHUONGXA", GlobalVariable.WSInfo.ConnectionString);
            dataAdapter.Fill(ds, "DM_PHUONGXA");
            repositoryItemLookUpEdit_PhuongXa.DataSource = ds.Tables["DM_PHUONGXA"];

            if (ds.Tables["DM_QUANHUYEN"] != null)
                ds.Tables["DM_QUANHUYEN"].Clear();
            dataAdapter = new SqlDataAdapter("SELECT * FROM DM_QUANHUYEN", GlobalVariable.WSInfo.ConnectionString);
            dataAdapter.Fill(ds, "DM_QUANHUYEN");
            repositoryItemLookUpEdit_QuanHuyen.DataSource = ds.Tables["DM_QUANHUYEN"];

            if (ds.Tables["DM_TINHTP"] != null)
                ds.Tables["DM_TINHTP"].Clear();
            dataAdapter = new SqlDataAdapter("SELECT * FROM DM_TINHTP", GlobalVariable.WSInfo.ConnectionString);
            dataAdapter.Fill(ds, "DM_TINHTP");
            repositoryItemLookUpEdit_TinhTP.DataSource = ds.Tables["DM_TINHTP"];

            if (ds.Tables["DM_DANTOC"] != null)
                ds.Tables["DM_DANTOC"].Clear();
            dataAdapter = new SqlDataAdapter("SELECT * FROM DM_DANTOC", GlobalVariable.WSInfo.ConnectionString);
            dataAdapter.Fill(ds, "DM_DANTOC");
            repositoryItemLookUpEdit_DanToc.DataSource = ds.Tables["DM_DANTOC"];

            if (ds.Tables["DM_TRANGTHAI"] != null)
                ds.Tables["DM_TRANGTHAI"].Clear();
            dataAdapter = new SqlDataAdapter("SELECT * FROM DM_TRANGTHAI", GlobalVariable.WSInfo.ConnectionString);
            dataAdapter.Fill(ds, "DM_TRANGTHAI");
            repositoryItemLookUpEdit_TrangThai.DataSource = ds.Tables["DM_TRANGTHAI"];

            if (ds.Tables["DM_CHEDO"] != null)
                ds.Tables["DM_CHEDO"].Clear();
            dataAdapter = new SqlDataAdapter("SELECT * FROM DM_CHEDO", GlobalVariable.WSInfo.ConnectionString);
            dataAdapter.Fill(ds, "DM_CHEDO");
            repositoryItemLookUpEdit_CheDo.DataSource = ds.Tables["DM_CHEDO"];

            if (ds.Tables["DM_TINHTRANG"] != null)
                ds.Tables["DM_TINHTRANG"].Clear();
            dataAdapter = new SqlDataAdapter("SELECT * FROM DM_TINHTRANG", GlobalVariable.WSInfo.ConnectionString);
            dataAdapter.Fill(ds, "DM_TINHTRANG");
            repositoryItemLookUpEdit_TinhTrang.DataSource = ds.Tables["DM_TINHTRANG"];

            //Ho so hoc sinh
            if (ds.Tables["DM_HOSOHOCSINH"] != null)
                ds.Tables["DM_HOSOHOCSINH"].Clear();
            dataAdapter = new SqlDataAdapter("SELECT * FROM DM_HOSOHOCSINH", GlobalVariable.WSInfo.ConnectionString);
            dataAdapter.Fill(ds, "DM_HOSOHOCSINH");
            repositoryItemLookUpEdit_MaHoSoHocSinh.DataSource = ds.Tables["DM_HOSOHOCSINH"];

            optCardView.Checked = true;
        }

        private void GetData(string strMaLop, bool bFirst)
        {
            try
            {

                if (ds.Tables["HOCSINH_LOP"] != null)
                    ds.Tables["HOCSINH_LOP"].Clear();
                if (ds.Tables["HOCSINH_HOSO"] != null)
                    ds.Tables["HOCSINH_HOSO"].Clear();
                if (ds.Tables["HOCSINH_HOCPHI"] != null)
                    ds.Tables["HOCSINH_HOCPHI"].Clear();
                if (ds.Tables["HOCSINH"] != null)
                    ds.Tables["HOCSINH"].Clear();

                string strSELE_HOCSINH_where = "";
                string strSELE_HOCSINH = "";
                string strSELE_HOCSINH_LOP = "";
                string strSELE_HOCSINH_HOSO = "";
                string strSELE_HOCPHI = "";

                if (strMaLop == "")  //Load tat ca hoc sinh
                {
                    strSELE_HOCSINH = "SELECT M.*, HoLot + ' '  + Ten as HoTen FROM HOCSINH M  " +
                                        "left join HOCSINH_LOP on M.MaHocSinh = HOCSINH_LOP.MaHocSinh " +
                                        "left join DM_LOP on HOCSINH_LOP.MaLop = DM_LOP.MaLop " +
                                        "WHERE MaNienHoc = '" +  txtMaNienHoc.Text +  "' ";
                    strSELE_HOCSINH_where = strSELE_HOCSINH;
                }
                else   //Load theo lop
                {
                    strSELE_HOCSINH_where = "SELECT M.*, HoLot + ' '  + Ten as HoTen FROM HOCSINH M " +
                                      "WHERE MaHocSinh IN (select MaHocSinh from HOCSINH_LOP where MaLop = '" + strMaLop + "' ) ";
                    strSELE_HOCSINH = "SELECT M.*, HoLot + ' '  + Ten as HoTen FROM HOCSINH M " +
                                      "WHERE MaHocSinh IN (select MaHocSinh from HOCSINH_LOP where MaLop = '" + strMaLop + "' ) " + 
                                      "ORDER BY Ten, HoLot ";
                }

                strSELE_HOCSINH_LOP = "SELECT * FROM HOCSINH_LOP D WHERE MaHocSinh IN (select MaHocSinh from (" + strSELE_HOCSINH_where + ") HS )" ;

                strSELE_HOCSINH_HOSO = "SELECT * FROM HOCSINH_HOSO HS WHERE MaHocSinh IN (select MaHocSinh from (" + strSELE_HOCSINH_where + ") HS )";
                
                strSELE_HOCPHI = "select MaLoaiChungTu, M.SoChungTu, NgayChungTu, MaNienHoc, M.MaLop, MaDoiTac, HOCSINH.MaHocSinh, HoTen, DienGiai, TaiKhoanNO, TaiKhoanCO, DienGiaiChiTiet, SoTien, SoTienMienGiam, D.GhiChu " +
                                "from TIENMAT M " +
                                "join TIENMAT_CHITIET D on M.SoChungTu = D.SoChungtu " +
                                "join HOCSINH_LOP on M.MaDoitac = HOCSINH_LOP.MaHocTap " +
                                "join HOCSINH on HOCSINH_LOP.MaHocSinh = HOCSINH.MaHocSinh " +
                                "where  HOCSINH_LOP.MaHocSinh IN (select MaHocSinh from (" + strSELE_HOCSINH_where + ") HS ) " +
                                "and MaLoaiChungTu <> 'KH'";

                dataAdapter = new SqlDataAdapter(strSELE_HOCSINH, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH");

                dataAdapter = new SqlDataAdapter(strSELE_HOCSINH_LOP, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH_LOP");
                
                dataAdapter = new SqlDataAdapter(strSELE_HOCSINH_HOSO, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH_HOSO");

                dataAdapter = new SqlDataAdapter(strSELE_HOCPHI, GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "HOCSINH_HOCPHI");

                //if (bFirst)
                if (ds.Relations.Count == 0) 
                {
                    ds.Tables["HOCSINH"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH"].Columns["MaHocSinh"] };
                    ds.Tables["HOCSINH_LOP"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH_LOP"].Columns["MaHocTap"] };
                    ds.Tables["HOCSINH_HOSO"].PrimaryKey = new DataColumn[] { ds.Tables["HOCSINH_HOSO"].Columns["ID"] };
                    ds.Tables["HOCSINH_HOSO"].Columns["ID"].AutoIncrement = true;
                    ds.Tables["HOCSINH_HOSO"].Columns["ID"].AutoIncrementStep = 1;

                    // Set relations
                    ds.Relations.Add("M_D", ds.Tables["HOCSINH"].Columns["MaHocSinh"],
                        ds.Tables["HOCSINH_LOP"].Columns["MaHocSinh"]);
                    ds.Relations.Add("M_HS", ds.Tables["HOCSINH"].Columns["MaHocSinh"],
                        ds.Tables["HOCSINH_HOSO"].Columns["MaHocSinh"]);
                    ds.Relations.Add("M_HP", ds.Tables["HOCSINH"].Columns["MaHocSinh"],
                        ds.Tables["HOCSINH_HOCPHI"].Columns["MaHocSinh"]);
                }

                gridHocSinh.DataSource = ds;
                gridHocSinh.DataMember = "HOCSINH";

                gridHocSinhLop.DataSource = ds;
                gridHocSinhLop.DataMember = "HOCSINH.M_D";

                grid_BienNhanHS.DataSource = ds;
                grid_BienNhanHS.DataMember = "HOCSINH.M_HS";
                
                gridHocPhi.DataSource = ds;
                gridHocPhi.DataMember = "HOCSINH.M_HP";

                //gridViewHocSinh.BestFitColumns();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error: \n" + ex.Message);
            }
        }

        private void txtMaNienHoc_TextChanged(object sender, EventArgs e)
        {
            try
            {
                if (txtMaNienHoc.Text == "") return;
                DataTable dt = new DataTable();
                SQLHelper.Fill(dt, GlobalVariable.WSInfo.ConnectionString, "SELECT * FROM DM_LOP WHERE MaNienHoc = '" + txtMaNienHoc.Text + "' Order by MaKhoi, STT");

                cboLop.DataSource = dt;
                cboLop.DisplayMember = "TenLop";
                cboLop.ValueMember = "MaLop";

                //Combo de chon lop trong luoi HOCSINH_LOP
                if (ds.Tables["DM_LOP"] != null)
                    ds.Tables["DM_LOP"].Clear();
                dataAdapter = new SqlDataAdapter("SELECT * FROM DM_LOP WHERE MaNienHoc = '" + txtMaNienHoc.Text + "' Order by MaKhoi, STT", GlobalVariable.WSInfo.ConnectionString);
                dataAdapter.Fill(ds, "DM_LOP");
                repositoryItemLookUpEdit_Lop.DataSource = ds.Tables["DM_LOP"];

                chkXemTatCa.Checked = false;
            }
            catch (Exception ex)
            { MessageBox.Show(ex.Message); }
        }

        private void cboLop_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (cboLop.SelectedIndex == -1 || cboLop.SelectedValue.ToString() == "") return;
            GetData(cboLop.SelectedValue.ToString(), false);
        }

        private void cmdUpDate_Click(object sender, EventArgs e)
        {
            try
            {
                //Update HOCSINH
                SqlConnection cnn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
                SqlDataAdapter sqldaHS = new SqlDataAdapter("SELECT * FROM HOCSINH", cnn);
                DataHelper.buildDataCommands(ref sqldaHS);
                sqldaHS.Update(ds, "HOCSINH");

                //Update HOCSINH_LOP
                SqlDataAdapter sqldaHSL = new SqlDataAdapter("SELECT * FROM HOCSINH_LOP", cnn);
                DataHelper.buildDataCommands(ref sqldaHSL);
                sqldaHSL.Update(ds, "HOCSINH_LOP");

                //Update HOCSINH_HOSO
                SqlDataAdapter sqldaHSHS = new SqlDataAdapter("SELECT * FROM HOCSINH_HOSO", cnn);
                DataHelper.buildDataCommands(ref sqldaHSHS);
                sqldaHSHS.Update(ds, "HOCSINH_HOSO");

                //Load lai
                GetData(cboLop.SelectedValue.ToString(), false);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        FormView fv = null;

        private void cmdThemMoi_Click(object sender, EventArgs e)
        {
            if (fv == null)
            {
                string strSELECT = "SELECT MaTuyenSinh as [Mã số], HoLot + ' ' + Ten as [Học sinh], case GioiTinh when 1 then N'Nam' else N'Nữ' end as [Giới tính], TenTinhTP as [Nơi ở], NgaySinh as [Ngày sinh], " +
                                   "MaKhoiDangKy as [Khối ĐK], MaCoSo as [Cơ sở], NgayNopHoSo as [Ngày nộp HS], HoTenPhuHuynh as [Phụ huynh], SoDienThoai as [Điện thoại] " +
                                   "FROM TUYENSINH M " +
                                   "LEFT JOIN DM_TinhTP ON M.MaTinhTP = DM_TinhTP.MaTinhTP " +
                                   "WHERE MaKetQuaXetTuyen = N'ĐẠT' or MaKetQuaXetTuyen = N'THI ĐẠT' ";

                fv = new FormView(strSELECT, GlobalVariable.WSInfo.ConnectionString, GlobalVariable.WSInfo.WorkingDate);
                fv.DateFilterColumns = new string[,] { { "NgayNopHoSo", "Ngày nộp hồ sơ" } };
                //fv.CollumnFocusInFilterGrid = 2;
            }
            fv.ShowDialog(this);
            if (fv.DialogResult == DialogResult.OK)
            {
                string strMaTuyenSinh = fv.SelectedIDs.Replace("('", "").Replace("')", "");
                string strMaHocSinh = "HS" + strMaTuyenSinh.Substring(2);
                string strMaHocTap = strMaHocSinh + "_" + cboLop.SelectedValue;
                //MessageBox.Show("MaTS: " + strMaTuyenSinh + "\nMa HS: " + strMaHocSinh + "\nMa HT: " + strMaHocTap);
                ThemMoi_HOCSINH(strMaHocSinh, strMaTuyenSinh);
                ThemMoi_HOCSINH_LOP(strMaHocTap, strMaHocSinh, cboLop.SelectedValue.ToString());
            }			
        }


        private void ThemMoi_HOCSINH(string pMaHocSinh, string pMaTuyenSinh)
        {
            try
            {
                DataRow dr = ds.Tables["HOCSINH"].NewRow();
                dr["MaHocSinh"] = pMaHocSinh;
                dr["MaTuyenSinh"] = pMaTuyenSinh;

                //Doc TUYENSINH lay thong tin dua vao HOCSINH
                SqlDataReader HV_DataReader = SQLHelper.ExecuteReader(GlobalVariable.WSInfo.ConnectionString,
                                                    "SELECT * FROM TUYENSINH " +
                                                    "WHERE MaTuyenSinh = '" + pMaTuyenSinh + "'");
                HV_DataReader.Read();
                dr["HoLot"] = HV_DataReader["HoLot"];
                dr["Ten"] = HV_DataReader["Ten"];
                dr["GioiTinh"] = HV_DataReader["GioiTinh"];
                dr["NgaySinh"] = HV_DataReader["NgaySinh"];
                dr["DiaChi"] = HV_DataReader["DiaChi"];
                dr["MaPhuongXa"] = HV_DataReader["MaPhuongXa"];
                dr["MaQuanHuyen"] = HV_DataReader["MaQuanHuyen"];
                dr["MaTinhTP"] = HV_DataReader["MaTinhTP"];
                if (HV_DataReader["QUANHE"].ToString() == "CHA") dr["HoTen_Cha"] = HV_DataReader["HoTenPhuHuynh"];
                if (HV_DataReader["QUANHE"].ToString() == "MẸ") dr["HoTen_Me"] = HV_DataReader["HoTenPhuHuynh"];
                dr["SoDienThoai"] = HV_DataReader["SoDienThoai"];
                dr["GhiChu"] = HV_DataReader["GhiChu"];

                ds.Tables["HOCSINH"].Rows.Add(dr);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void ThemMoi_HOCSINH_LOP(string pMaHocTap, string pMaHocSinh, string pMaLop)
        {
            try
            {
                DataRow dr = ds.Tables["HOCSINH_LOP"].NewRow();
                dr["MaHocTap"] = pMaHocTap;
                dr["MaHocSinh"] = pMaHocSinh;
                dr["MaLop"] = pMaLop;
                
                ds.Tables["HOCSINH_LOP"].Rows.Add(dr);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

        }

        private void cmdCancel_Click(object sender, EventArgs e)
        {
            ds.RejectChanges();
        }

        private void xtraTabPage1_Paint(object sender, PaintEventArgs e)
        {

        }

        private void chkXemTatCa_CheckedChanged(object sender, EventArgs e)
        {
            if (cboLop.SelectedIndex == -1 || cboLop.Text == "" || txtMaNienHoc.Text == "" ) return;
            
            if (chkXemTatCa.Checked)
                GetData("", false);
            else
                GetData(cboLop.SelectedValue.ToString() , false);

        }

        private void optLayoutView_CheckedChanged(object sender, EventArgs e)
        {
            gridHocSinh.MainView = cardView;
        }

        private void optGridView_CheckedChanged(object sender, EventArgs e)
        {
            gridHocSinh.MainView = gridView;
        }

        private void cmdBienNhanHoSo_Click(object sender, EventArgs e)
        {
            try
            {

                cmdBienNhanHoSo.Enabled = false;

                //Load report
                ReportDocument rpt = new ReportDocument();
                rpt.Load(Application.StartupPath + "\\Report\\Report_HocVu_BienNhanHoSo.rpt");

                //Set connection
                PublicFunction.SetDBLogonForReport(rpt);

                //Set parameter
                BindingManagerBase bmb = this.BindingContext[ds, "HOCSINH"];
                PublicFunction.SetParameter(rpt, "@pMaHocSinh", ds.Tables["HOCSINH"].Rows[bmb.Position]["MaHocSinh"].ToString());
                PublicFunction.SetParameter(rpt, "@pNgayNhanHoSo", DateTime.Now.Date );
                //PublicFunction.SetParameter(rpt, "pThuquy", GlobalVariable.WSInfo["Sys.UserName.Thuquy"]);
                //PublicFunction.SetParameter(rpt, "pKetoantruong", GlobalVariable.WSInfo["Sys.UserName.KetoanTruong"]);
                //PublicFunction.SetParameter(rpt, "pGiamDoc", GlobalVariable.WSInfo["Sys.UserName.Giamdoc"]);

                //View report
                frmPrintPreview frmPP = new frmPrintPreview(rpt);
                frmPP.ShowDialog();
                frmPP.Dispose();

                rpt.Dispose();
                cmdBienNhanHoSo.Enabled = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    

    }
}
